/** PreparedStatement:
   A prepared statement is a SQL statement with parameters and it is precompiled
at the time the statement is created. It is compiled once before the first time
execution, and executed over and over again without compilation. The prepared
statement can be executed efficiently than dynamic SQL statement.

   To create a prepared statement,
	1. define a PreparedStatement object (PreparedStatement pStmt ),
	2. Call Connection.prepareStatement( sqlString ),
	3. Use ? to place a parameter in the sqlString.

   To execute the sql statement,
	1. clear old parameters ( pStmt.clearParameters() )
	2. set new parameter with pStmt.setXXX(int, sqlType), or setObject(...).
	   While setXXX() will set a primitive and a few other java types such as Date,
	   String, CLOB, the three forms of setObject() allow you convert a java object
	   to specified SQL data type object. You have control over the conversion.
	3. call executeQuery() for SQL select, executeUpdate() for update, delete
	   insert statement, or execute() for sql returnning multiple resultsets,
	   or update counts.
*/

import java.sql.*;
import java.io.*;

public class PreparedStatement1 {

	Connection cnn = null;
	PreparedStatement  pStmt = null;
	ResultSet  res = null ;
	ResultSetMetaData meta = null;

	String url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
		 user="scott", passwd="tiger";

	public PreparedStatement1() {
	   try {
             	try { Class.forName("oracle.jdbc.driver.OracleDriver"); }
		catch (ClassNotFoundException ee) { ee.printStackTrace(); System.exit(-1); }

		cnn = DriverManager.getConnection(url, user, passwd);
		pStmt = cnn.prepareStatement("select * from Emp where job like ?" );
	   } catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }
	}

	public static void main(String argv[]) {
		PreparedStatement1 ex2 = new PreparedStatement1(); // get connected, and create stmt.
		byte ans[] = new byte[120];
		char c = ' ';
		String	jTitle;

		while ( true ) {
			for ( int i = 0; i < ans.length; i ++ ) ans[i] = (byte) ' ';
			System.out.print("Enter a job title (%) to list (all) employees or Q/e to quite ... ");
			try { System.in.read(ans); } catch (IOException e) {}
			jTitle =(new String(ans)).trim();
			if ( jTitle == null || jTitle.equals("") ) continue;
			try { c = jTitle.charAt(0); } catch (StringIndexOutOfBoundsException e) {}
			if ( c == 'q' || c == 'Q' ) { ex2.quit(); }
			ex2.getEmployeeByJobTitle(jTitle);
			ex2.displayResultSet();
		}
	}

	void getEmployeeByJobTitle(String job) {
	   try {
		System.out.println("\n");
		pStmt.clearParameters();
		pStmt.setString(1, job.toUpperCase());
		res = pStmt.executeQuery();
		if ( meta == null ) meta = res.getMetaData();
	   } catch (SQLException e) { e.printStackTrace(); }
	}

	void displayResultSet() {
	   try {
		int colCnt = meta.getColumnCount();
		displayColumnHeaders(colCnt);
		while ( res.next() ) {
			for ( int i = 1; i <= colCnt; i ++ )
				System.out.print(ScreenIO.expandString( res.getString(i), 15 )) ;
			System.out.println();
		}	
	   } catch (SQLException e) { e.printStackTrace();  /*System.exit(-1);*/ }
	     catch (NullPointerException e ) {}
	}

	void displayColumnHeaders(int cnt) {
	   try {
		for ( int i = 1; i <= cnt; i ++ )
			System.out.print( ScreenIO.expandString(meta.getColumnName(i), 15));
		System.out.println();

		for ( int i = 1; i <= cnt; i ++ )
			System.out.print( ScreenIO.expandString("===========", 15));
		System.out.println();
	   } catch (SQLException e ) {}
	
	}
	protected void quit() {
	   try {
		pStmt.close();
		res.close();
		cnn.close();
		System.exit(0);
	   } catch (SQLException e) {}
	   try { super.finalize(); } catch (Throwable e) {}
	}
}
